package com.aternity.geolocations;

import au.com.bytecode.opencsv.CSVWriter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

/**
 *
 * @author Daniel Orr
 */
public class CreateCsvFromFirebird {

    
    static final String query = "select distinct\n"
            + "        countrysyn.\"Name\" as \"country\", \n"
            + "        statesyn.\"Name\" as \"state\",  \n"
            + "        citysyn.\"Name\" as \"city\"\n"
            + "from \n"
            + "    \"City\" city\n"
            + "    join \"CitySynonyms\" citysyn on city.ID=citysyn.\"ParentID\"\n"
            + "    join \"State\" state on state.ID=city.\"ParentID\"\n"
            + "    join \"StateSynonyms\" statesyn on state.ID=statesyn.\"ParentID\"\n"
            + "    join \"Country\" country on state.\"ParentID\"=country.ID\n"
            + "    join \"CountrySynonyms\" countrysyn on country.ID=countrysyn.\"ParentID\"\n"
            + "  where\n"
            + "      (countrysyn.\"Locale\"='en_US' or countrysyn.\"Locale\" is NULL)\n"
            + "      and countrysyn.\"IsDisplayName\" = 1\n"
            + "      and (statesyn.\"Locale\"='en_US' or statesyn.\"Locale\" is NULL)\n"
            + "      and statesyn.\"IsDisplayName\" = 1\n"
            + "      and (citysyn.\"Locale\"='en_US' or citysyn.\"Locale\" is NULL)\n"
            + "      and citysyn.\"IsDisplayName\" = 1\n"
            + " order by\n"
            + "     \"country\", \"state\", \"city\"";
    
    public static void main(String[] args) throws Exception {
        Class.forName("org.firebirdsql.jdbc.FBDriver");
        Properties props = new Properties();
        props.setProperty("user", "SYSDBA");
        props.setProperty("password", "masterkey");
        props.setProperty("encoding", "UNICODE_FSS");
        Connection connection = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:C:/dev/geoloc/GEOCODING.fdb", props);
        
        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream("country_state_city.csv"), "UTF-8"))) {
            final PreparedStatement ps = connection.prepareStatement(query);
            final ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                String country = rs.getString(1);
                String state = rs.getString(2);
                String city = rs.getString(3);
                csvWriter.writeNext(new String[]{country, state, city});
            }
        }
    }
    
}
